﻿/*=================================================================
* 版权信息：Copyright (C) 2005-2011 捷点科技 All Rights Reserved.
* 文件名称：CNVP_OrderProducts.cs
* 创建标识：曾乃强 15158756652 2011-03-17 11:03:01
* 修改标识：
* 功能描述：
===================================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework.Helper;
using CNVP.Config;

namespace CNVP.JShop.DAL
{
    /// <summary>
    /// 对*CNVP_OrderProducts*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class CNVP_OrderProducts
    {
       private string Prefix = DBConfig.Prefix;
        #region DAL
        public int Insert(Model.CNVP_OrderProducts obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into  " + Prefix + "OrderProducts ([ProductName],[ImgProLoad],[ProductClassID],[ProductID],[ProductsDetailID],[ProductNo],[Price],[Num],[TotalPrice],[OrderID])");
            sql.Append("values(@ProductName,@ImgProLoad,@ProductClassID,@ProductID,@ProductsDetailID,@ProductNo,@Price,@Num,@TotalPrice,@OrderID)");
            
            SqlParameter[] pra = {
                            new SqlParameter("@ProductName",obj.ProductName),
                            new SqlParameter("@ImgProLoad",obj.ImgProLoad), 
                            new SqlParameter("@ProductClassID",obj.ProductClassID),
                            new SqlParameter("@ProductID",obj.ProductID),
                            new SqlParameter("@ProductsDetailID",obj.ProductsDetailID),
							new SqlParameter("@ProductNo",obj.ProductNo),
							new SqlParameter("@Price",obj.Price),
							new SqlParameter("@Num",obj.Num),
							new SqlParameter("@TotalPrice",obj.TotalPrice),
                            new SqlParameter("@OrderID",obj.OrderID)
                                 };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int Update(Model.CNVP_OrderProducts obj)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "OrderProducts set [Price] = @Price,[Num] = @Num,[TotalPrice]=@TotalPrice where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@Price",obj.Price),
							new SqlParameter("@Num",obj.Num),
                            new SqlParameter("@TotalPrice",obj.TotalPrice)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public int UpdateBuyerRate(Model.CNVP_OrderProducts obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "OrderProducts set [BuyerRate] = @BuyerRate,[ReviewID] = @ReviewID where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@BuyerRate",obj.BuyerRate),
                            new SqlParameter("@ReviewID",obj.ReviewID)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int Delete(Model.CNVP_OrderProducts obj)
        {
           StringBuilder sql = new StringBuilder();
           sql.Append("delete from " + Prefix + "OrderProducts where [ID] = @ID");
           SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
           return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public Model.CNVP_OrderProducts LoadById(int ID)
        {
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ProductName],[ImgProLoad],[ProductClassID],[ProductID],[ProductsDetailID],[ProductNo],[Price],[Num],[TotalPrice],[OrderID] from " + Prefix + "OrderProducts");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            Model.CNVP_OrderProducts cnvp_orderproducts = new Model.CNVP_OrderProducts();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_orderproducts.ProductName = dr["ProductName"] is DBNull ? String.Empty : (string)dr["ProductName"];
                cnvp_orderproducts.ImgProLoad = dr["ImgProLoad"] is DBNull ? String.Empty : (string)dr["ImgProLoad"];
                cnvp_orderproducts.ProductClassID = dr["ProductClassID"] is DBNull ? 0 : (int)dr["ProductClassID"];
                cnvp_orderproducts.ProductID = dr["ProductID"] is DBNull ? 0 : (int)dr["ProductID"];
                cnvp_orderproducts.ProductsDetailID = dr["ProductsDetailID"] is DBNull ? 0 : (int)dr["ProductsDetailID"];
                cnvp_orderproducts.ProductNo = dr["ProductNo"] is DBNull ? String.Empty : (string)dr["ProductNo"];
                cnvp_orderproducts.Price = dr["Price"] is DBNull ? (decimal)0.00 : (decimal)dr["Price"];
                cnvp_orderproducts.Num = dr["Num"] is DBNull ? 0 : (int)dr["Num"];
                cnvp_orderproducts.TotalPrice = dr["TotalPrice"] is DBNull ? 0 : (decimal)dr["TotalPrice"];
                cnvp_orderproducts.Num = dr["OrderID"] is DBNull ? 0 : (int)dr["OrderID"];
            }
             return cnvp_orderproducts;
        }

        #endregion

        public List<Model.CNVP_OrderProducts> LoadAll(int OrderID)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[ProductName],[ImgProLoad],[ProductClassID],[ProductID],[ProductsDetailID],[ProductNo],[Price],[Num],[TotalPrice],[BuyerRate],[ReviewID] from " + Prefix + "OrderProducts");
            sql.Append(" where [OrderID] = @OrderID");
            SqlParameter[] pra = {
                                 new SqlParameter("@OrderID",OrderID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            List<Model.CNVP_OrderProducts> list = new List<Model.CNVP_OrderProducts>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_OrderProducts cnvp_orderproducts = new Model.CNVP_OrderProducts();
                    cnvp_orderproducts.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_orderproducts.ProductName = dr["ProductName"] is DBNull ? String.Empty : (string)dr["ProductName"];
                    cnvp_orderproducts.ImgProLoad = dr["ImgProLoad"] is DBNull ? String.Empty : (string)dr["ImgProLoad"];
                    cnvp_orderproducts.ProductClassID = dr["ProductClassID"] is DBNull ? 0 : (int)dr["ProductClassID"];
                    cnvp_orderproducts.ProductID = dr["ProductID"] is DBNull ? 0 : (int)dr["ProductID"];
                    cnvp_orderproducts.ProductsDetailID = dr["ProductsDetailID"] is DBNull ? 0 : (int)dr["ProductsDetailID"];
                    cnvp_orderproducts.ProductNo = dr["ProductNo"] is DBNull ? String.Empty : (string)dr["ProductNo"];
                    cnvp_orderproducts.Price = dr["Price"] is DBNull ? (decimal)0.00 : (decimal)dr["Price"];
                    cnvp_orderproducts.Num = dr["Num"] is DBNull ? 0 : (int)dr["Num"];
                    cnvp_orderproducts.TotalPrice = dr["TotalPrice"] is DBNull ? 0 : (decimal)dr["TotalPrice"];
                    cnvp_orderproducts.BuyerRate = dr["BuyerRate"] is DBNull ? (byte)0 : (byte)dr["BuyerRate"];
                    cnvp_orderproducts.ReviewID = dr["ReviewID"] is DBNull ? 0 : (int)dr["ReviewID"];
                    list.Add(cnvp_orderproducts);
                }
            } 
            return list;

        }

        public List<Model.CNVP_OrderProducts> LoadAllByProductsIDAndBuyer(int ProductsID, int BuyerID,string OrderState)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select b.[ID] from " + Prefix + "Order as a left join " + Prefix + "OrderProducts as b on a.ID = b.OrderID where a.BuyerID=@BuyerID and b.ProductID=@ProductsID and b.BuyerRate=0 and a.OrderState in (" + OrderState + ") ");
            SqlParameter[] pra = {
                                 new SqlParameter("@ProductsID",ProductsID),
						         new SqlParameter("@BuyerID",BuyerID)
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra);
            List<Model.CNVP_OrderProducts> list = new List<Model.CNVP_OrderProducts>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_OrderProducts cnvp_orderproducts = new Model.CNVP_OrderProducts();
                    cnvp_orderproducts.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    list.Add(cnvp_orderproducts);
                }
            }
            return list;
        }

        public List<Model.CNVP_OrderProducts> LoadAllByOrderProductsIDAndBuyer(int OrderProductsID, int BuyerID, string OrderState)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select b.[ID],b.[ProductName] from " + Prefix + "Order as a left join " + Prefix + "OrderProducts as b on a.ID = b.OrderID where a.BuyerID=@BuyerID and b.ID=@OrderProductsID and b.BuyerRate=0 and a.OrderState in (" + OrderState + ")");
            SqlParameter[] pra = {
                                 new SqlParameter("@OrderProductsID",OrderProductsID),
						         new SqlParameter("@BuyerID",BuyerID)
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            List<Model.CNVP_OrderProducts> list = new List<Model.CNVP_OrderProducts>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_OrderProducts cnvp_orderproducts = new Model.CNVP_OrderProducts();
                    cnvp_orderproducts.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_orderproducts.ProductName = dr["ProductName"] is DBNull ? String.Empty : (string)dr["ProductName"];
                    list.Add(cnvp_orderproducts);
                }
            }
            return list;
        }
    }
}

